/*
 * Copyrights of MYeBills. Do not copy or distribute without permissions.
 *
 * TODO 
 */
package com.mbbmap.app.home;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbbmap.app.dao.UserRoleDao;
import com.mbbmap.app.manager.ConfigManager;
import com.mbbmap.app.manager.DBConnectionManager;
import com.mbbmap.app.manager.SystemLogManager;
import com.mbbmap.util.Constants;
/**
 * @author Lue
 *
 * Main class to access Payee application database
 */
public class DBAccessHome {
    
    private static DBAccessHome instance = null;
    SystemLogManager sysLogMgr;
    
    /**
     * create an instance of this class
     * @return this class
     */
    public static DBAccessHome getInstance() {
		//thread-safe implementation
		if (instance == null) {
			synchronized (DBAccessHome.class) {
				if (instance == null) {
					instance = new DBAccessHome();
				}
			}
		}
		return instance;
    }
    
    private DBAccessHome () {
        super();
        sysLogMgr = SystemLogManager.getInstance();
    }
    
    /**
     * get DB Connection from connection pool
     * @return DB Connection
     */
    public Connection getDBConnection() {
		int TIMEOUT = Constants.DB_CONNECTION_TIMEOUT; //DB connection timeout if unable to get connection in less than 10 seconds
		DBConnectionManager connMgr;
		String connectionPool;
	
		connectionPool = ConfigManager.getInstance().getConnectionPool();

		connMgr = DBConnectionManager.getInstance();
		String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
		
		if (timeOutValue != null) {
			try {
				TIMEOUT = Integer.parseInt(timeOutValue);
			} catch (NumberFormatException e) {
			    sysLogMgr.logMsg("Invalid DB timeout value : " + TIMEOUT + "\n" + e);
			}
		}
		//System.out.println("------------DBAccessHome.getDBConnection() connectionPool=" +connectionPool );
		//System.out.println("------------DBAccessHome.getDBConnection() timeOutValue=" +TIMEOUT );
		Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
		
		if (conn == null) {
		    System.out.println("------------DBAccessHome.getDBConnection() conn== null" );
		}
		
		return conn;
    }
    
    /**
     * free DB connection back to connection pool
     * @param conn
     */
    public void freeDBConnection (Connection conn) {
        
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        connMgr.freeConnection(connectionPool, conn);
    }
    
    
    public static void main (String args[]) {
        DBAccessHome dba = DBAccessHome.getInstance();
        dba.test();
    }
    /**
     * test JDBC connectivity
     */
    public void test () {
        
        
        System.out.println("----------------->>>>>test()");
        String querySql = null;
        Connection connection=null;
        PreparedStatement pstmtQuery = null;
        try {
            connection = getDBConnection(); 
            //java.sql.Driver d = (java.sql.Driver) Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
            
            //String connectionUrl = "jdbc:sqlserver://127.0.0.1;databaseName=eremit;user=sa;password=bgadmin;";
            
            //connection = d.connect(connectionUrl, new Properties());
            
            if (connection == null) {
                System.out.println(".....Connection is null");
            }
            querySql = "select mer_name from  TBLACCESSMODULE";
            pstmtQuery =  connection.prepareStatement(querySql);

            // execute query
            ResultSet rs = pstmtQuery.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString(1));
            } 
            
            rs.close();
            
        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("Error in SQL execution : " + querySql + "\n" + sqle);
            sqle.printStackTrace();
        } catch (Exception ex) {
            sysLogMgr.logMsg("Error listing user roles\n" + ex);
            ex.printStackTrace();
        } catch (Throwable t) {
            sysLogMgr.logMsg("Error listing user roles\n" + t);
            t.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
		            sysLogMgr.logMsg("Error closing pstmtQuery \n" + e1);
		            e1.printStackTrace();
				}
			}
            freeDBConnection (connection);
		}
    }

    
    /**
     * Get all the user roles available
     * @param merchantId
     * @return
     */
    public ArrayList getUserRoles (String merchantId) {
        String querySql = null;
        UserRoleDao role = null;
        ArrayList roles = new ArrayList();
        Connection connection=null;
        PreparedStatement pstmtQuery = null;
        try {
            connection = getDBConnection(); 
            
            if (connection == null) {
                System.out.println(".....Connection is null");
            }
            querySql = "select distinct role_code, role_name from  " + Constants.DB_TBL_ACCESSROLE + " where mer_name=? ";
            pstmtQuery =  connection.prepareStatement(querySql);
            pstmtQuery.setString(1, merchantId);

            // execute query
            ResultSet rs = pstmtQuery.executeQuery();
            while (rs.next()) {
                role = new UserRoleDao();
                role.setRoleId(rs.getString(1));
                role.setRoleName(rs.getString(2));
                
                roles.add(role);
            } 
            
            rs.close();
            
        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("Error in SQL execution : " + querySql + "\n" + sqle);
            sqle.printStackTrace();
            roles=null;
        } catch (Exception ex) {
            sysLogMgr.logMsg("Error listing user roles\n" + ex);
            ex.printStackTrace();
            roles=null;
        } catch (Throwable t) {
            sysLogMgr.logMsg("Error listing user roles\n" + t);
            t.printStackTrace();
            roles=null;
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
		            sysLogMgr.logMsg("Error closing pstmtQuery for listing user roles\n" + e1);
		            e1.printStackTrace();
				}
			}
            freeDBConnection (connection);
		}
        
        return roles;
    }
    
    
    /**
     * Insert activity log in audit log table
     * @param loginId
     * @param fileName
     * @param fileDate
     * @param payeeCode
     * @param fileType
     * @param version
     * @param action
     * @param remark
     * @return
     */
    /*public int insertAuditLog(String loginId, String fileName, java.sql.Date fileDate, String payeeCode, String fileType, int version, String action, String remark) {
        
        Connection connection = null;
        PreparedStatement pstmtInsert = null;
        int noOfRecords=0;
        
        // AUDIT LOG
        String insertSql = "INSERT INTO " + Constants.DB_TBLPAYEE_AUDIT_LOG + " (LOGIN_ID, FILE_NAME, FILE_DATE, PAYEE_CODE, FILE_TYPE, VERSION, ACTION, EVENT_TIMESTAMP, REMARK)" +
        " VALUES (?, ?, ?, ?, ?, ?, ?, SYSTIMESTAMP, ? )";
        
        try {
            connection = getDBConnection();
            
	        pstmtInsert = connection.prepareStatement(insertSql);
	        pstmtInsert.setString(1, loginId);
	        pstmtInsert.setString(2, fileName);
	        pstmtInsert.setDate(3, fileDate);
	        pstmtInsert.setString(4, payeeCode);
	        pstmtInsert.setString(5, fileType);
	        pstmtInsert.setInt(6, version);
	        pstmtInsert.setString(7, action);
	        pstmtInsert.setString(8, remark);
	        
	            
	        // execute the insert statement;
	        noOfRecords = pstmtInsert.executeUpdate();
	        
        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("Error in insertAuditLog SQL execution : " + insertSql + "\n" + sqle);
            sqle.printStackTrace();
        } catch (Exception ex) {
            sysLogMgr.logMsg("Error in insertAuditLog\n" + ex);
            ex.printStackTrace();
        } catch (Throwable t) {
            sysLogMgr.logMsg("Error in insertAuditLog\n" + t);
            t.printStackTrace();
        } finally {
			if (pstmtInsert != null) {
				try {
				    pstmtInsert.close();
				} catch (SQLException e1) {
		            sysLogMgr.logMsg("Error closing pstmtInsert for insertAuditLog\n" + e1);
		            e1.printStackTrace();
				}
			}
            freeDBConnection (connection);
		}
        
        return noOfRecords;

    }*/
    
    
    /**
     *  Get the Audit Log records
     *  Search criteria as given by the parameters
     * @param loginId
     * @param fileType
     * @param fileDateFrom in format : "dd/MM/yyyy"
     * @param fileDateTo in format : "dd/MM/yyyy"
     * @param payeeCode
     * @param eventDateFrom in format : "dd/MM/yyyy"
     * @param eventDateTo in format : "dd/MM/yyyy"
     * @param page Pagination class - to setup records to select and return
     * @return ArrayList of AuditLogDao
     * @throws ResultSetTooBigException (if result has too many records)
     */
    /*public ArrayList getAuditLog (String loginId, String fileType, String fileDateFrom, String fileDateTo, String payeeCode, String eventDateFrom, String eventDateTo, Pagination page) {
        
        ArrayList auditLogs = new ArrayList();
        Date dtFileDateFrom = null, dtFileDateTo = null, dtEventDateFrom = null, dtEventDateTo = null;
        StringBuffer querySql = new StringBuffer("select login_id, file_name, file_date, payee_code, file_type, version, action, event_timestamp, remark from " 
                + Constants.DB_TBLPAYEE_AUDIT_LOG);
        Connection connection = null;
        PreparedStatement pstmtQuery = null;
        boolean bLoginId = false, bFileType=false, bFileDateFrom=false, bFileDateTo=false, bPayeeCode=false, bEventDateFrom=false, bEventDateTo=false;
        
        try {
            StringBuffer tmpSql = new StringBuffer("");
            
            if (loginId !=null  && !loginId.trim().equals("")) {
                bLoginId = true;
                tmpSql.append(" LOWER(login_id) like ? ");
            }
            
            if (fileType !=null  && !fileType.trim().equals("")) {
                bFileType = true;
                if (!tmpSql.toString().trim().equals(""))
                    tmpSql.append(" and ");
                
                tmpSql.append(" LOWER(file_type) like ? ");
            }
            
            if (fileDateFrom !=null  && !fileDateFrom.trim().equals("")) {
                bFileDateFrom = true;
                if (!tmpSql.toString().trim().equals(""))
                    tmpSql.append(" and ");
                
                tmpSql.append(" file_date >=? ");
                dtFileDateFrom = DateUtil.createDateLocalSlash(fileDateFrom.trim());
            }
            
            if (fileDateTo !=null  && !fileDateTo.trim().equals("")) {
                bFileDateTo = true;
                if (!tmpSql.toString().trim().equals(""))
                    tmpSql.append(" and ");
                
                tmpSql.append(" file_date <=? ");
                dtFileDateTo = DateUtil.createDateLocalSlash(fileDateTo.trim());
            }
            
            if (payeeCode !=null  && !payeeCode.trim().equals("")) {
                bPayeeCode = true;
                if (!tmpSql.toString().trim().equals(""))
                    tmpSql.append(" and ");
                
                tmpSql.append(" LOWER(payee_code) like ? ");
            }
            
            if (eventDateFrom !=null  && !eventDateFrom.trim().equals("")) {
                bEventDateFrom = true;
                if (!tmpSql.toString().trim().equals(""))
                    tmpSql.append(" and ");
                
                tmpSql.append(" event_timestamp >=? ");
                dtEventDateFrom = DateUtil.createDateLocalSlash(eventDateFrom.trim());
            }
            
            if (eventDateTo !=null  && !eventDateTo.trim().equals("")) {
                bEventDateTo = true;
                if (!tmpSql.toString().trim().equals(""))
                    tmpSql.append(" and ");
                
                tmpSql.append(" event_timestamp <=? ");
                dtEventDateTo = DateUtil.createDateLocalSlash(eventDateTo.trim());
            }
            
            /////////////////////////////////////////////////////////////
            
            if (!tmpSql.toString().trim().equals("")) {
                querySql.append(" where ").append(tmpSql);
            }
            
            querySql.append(" order by payee_code, file_type, file_name, login_id, event_timestamp desc");

            System.out.println("DBAccessHome.getAuditLog() : SQL=" + querySql.toString());
            
            connection = getDBConnection(); 
            pstmtQuery =  connection.prepareStatement(querySql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            
            int col=1;
            
            if (bLoginId) {
                pstmtQuery.setString(col, "%"+loginId.toLowerCase().trim()+"%");
                col++;
            }
            
            if (bFileType) {
                pstmtQuery.setString(col, "%"+fileType.toLowerCase().trim()+"%");
                col++;
            }
            
            if (bFileDateFrom) {
                pstmtQuery.setDate(col, new java.sql.Date(dtFileDateFrom.getTime()));
                col++;
            }

            if (bFileDateTo) {
                pstmtQuery.setDate(col, new java.sql.Date(dtFileDateTo.getTime()));
                col++;
            }
            
            if (bPayeeCode) {
                pstmtQuery.setString(col, "%"+payeeCode.toLowerCase().trim()+"%");
                col++;
            }
            
            if (bEventDateFrom) {
                pstmtQuery.setDate(col, new java.sql.Date(dtEventDateFrom.getTime()));
                col++;
            }

            if (bEventDateTo) {
                pstmtQuery.setDate(col, new java.sql.Date(dtEventDateTo.getTime()));
                col++;
            }

            // execute query
            ResultSet rs = pstmtQuery.executeQuery();
            AuditLogDao audit;
            
            // if no result return null
            if (!rs.last()) {
                rs.close();
                //pstmtQuery.close();
                //connection.close();
                auditLogs = null;
                
            } else {
                int noOfRows = rs.getRow();
                if (page.getTotalRecord() <= 0 ) {
                    page.setTotalRecord(noOfRows);
                }
                //rs.beforeFirst();
                int startRow = page.getPageRecordStartNo();
                int endRow = page.getPageRecordEndNo();
                
                // move cursor to the row right before the first record to read
                rs.absolute(startRow);
                int counter = startRow;
                
                do {
                    audit = new AuditLogDao();
                    audit.setLoginId(rs.getString(1));
                    audit.setFileName(rs.getString(2));
                    audit.setFileDate(DateUtil.formatLocalSlash(rs.getDate(3)));
                    audit.setPayeeCode(rs.getString(4));
                    audit.setFileType(rs.getString(5));
                    audit.setVersion(Integer.toString(rs.getInt(6)));
                    audit.setAction(rs.getString(7));
                    audit.setTimestamp(DateUtil.formatLocalDateTime(rs.getDate(8)));
                    audit.setRemark(rs.getString(9));
                    
                    auditLogs.add(audit);
                    counter++;
                } while (counter <= endRow && rs.next());

            }
        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("Error in SQL execution : " + querySql + "\n" + sqle);
            sqle.printStackTrace();
            auditLogs = null;
        } catch (ParseException pe) {
            sysLogMgr.logMsg("Error in input date format" + pe);
            pe.printStackTrace();
            auditLogs = null;
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
		            sysLogMgr.logMsg("Error closing pstmtQuery for getAuditLog\n" + e1);
		            e1.printStackTrace();
				}
			}
            freeDBConnection (connection);
		}
        
        return auditLogs;
        
    }*/
        
}
